分析 S3或HDFS 上的文件

更新时间：2025-05-23

通过 Table Value Function 功能，PALO 可以直接将对象存储或 HDFS 上的文件作为 Table 进行查询分析。并且支持自动的列类型推断。

基础使用

这里我们通过 S3 Table Value Function 举例说明如何对对象存储上的文件进行分析。

查询

SELECT * FROM S3 (
    'uri' = 's3://bucket/path/to/tvf_test/test.parquet',
    'format' = 'parquet',
    's3.endpoint' = 'https://s3.us-east-1.amazonaws.com',
    's3.region' = 'us-east-1',
    's3.access_key' = 'ak',
    's3.secret_key'='sk'
)

其中 S3(...)是一个 TVF（Table Value Function）。Table Value Function 本质上是一张表，因此他可以出现在任意 SQL 语句中“表”可以出现的位置上。

TVF 的属性包括要分析的文件路径，文件格式、对象存储的连接信息等。其中文件路径（URI）可以使用通配符匹配多个文件，以下的文件路径都是合法的：

匹配指定的文件

s3://bucket/path/to/tvf_test/test.parquet
匹配所有 test_ 开头的文件

s3://bucket/path/to/tvf_test/test_*
匹配所有 .parquet 后缀的文件

s3://bucket/path/to/tvf_test/*.parquet
匹配 tvf_test目录下的所有文件

s3://bucket/path/to/tvf_test/*
匹配文件名中包含 test的文件

s3://bucket/path/to/tvf_test/*test*

自动推断文件列类型

可以通过 DESC FUNCTION 语法可以查看 TVF 的 Schema：

DESC FUNCTION s3 (
    "URI" = "s3://bucket/path/to/tvf_test/test.parquet",
    "s3.access_key"= "ak",
    "s3.secret_key" = "sk",
    "format" = "parquet",
    "use_path_style"="true"
);
+---------------+--------------+------+-------+---------+-------+
| Field         | Type         | Null | Key   | Default | Extra |
+---------------+--------------+------+-------+---------+-------+
| p_partkey     | INT          | Yes  | false | NULL    | NONE  |
| p_name        | TEXT         | Yes  | false | NULL    | NONE  |
| p_mfgr        | TEXT         | Yes  | false | NULL    | NONE  |
| p_brand       | TEXT         | Yes  | false | NULL    | NONE  |
| p_type        | TEXT         | Yes  | false | NULL    | NONE  |
| p_size        | INT          | Yes  | false | NULL    | NONE  |
| p_container   | TEXT         | Yes  | false | NULL    | NONE  |
| p_retailprice | DECIMAL(9,0) | Yes  | false | NULL    | NONE  |
| p_comment     | TEXT         | Yes  | false | NULL    | NONE  |
+---------------+--------------+------+-------+---------+-------+

PALO 根据以下规则推断 Schema：

对于 Parquet、ORC 格式，PALO 会根据文件元信息获取 Schema。
对于匹配多个文件的情况，会使用第一个文件的 Schema 作为 TVF 的 Schema。
对于 CSV、JSON 格式，PALO 会根据字段、分隔符等属性，解析第一行数据获取 Schema。

默认情况下，所有列类型均为 string。可以通过 csv_schema 属性单独指定列名和列类型。PALO 会使用指定的列类型进行文件读取。格式如下：name1:type1;name2:type2;...。如：
```
S3 (
    'uri' = 's3://bucket/path/to/tvf_test/test.parquet',
    's3.endpoint' = 'https://s3.us-east-1.amazonaws.com',
    's3.region' = 'us-east-1',
    's3.access_key' = 'ak'
    's3.secret_key'='sk',
    'format' = 'csv',
    'column_separator' = '|',
    'csv_schema' = 'k1:int;k2:int;k3:int;k4:decimal(38,10)'
)
```
当前支持的列类型名称如下：

列类型名称

tinyint

smallint

int

bigint

largeint

float

double

decimal(p,s)

date

datetime

char

varchar

string

boolean
对于格式不匹配的列（比如文件中为字符串，用户定义为 int；或者其他文件和第一个文件的 Schema 不相同），或缺失列（比如文件中有 4 列，用户定义了 5 列），则这些列将返回 null。

列类型名称
tinyint
smallint
int
bigint
largeint
float
double
decimal(p,s)
date
datetime
char
varchar
string
boolean

适用场景

查询分析

TVF 非常适用于对存储系统上的独立文件进行直接分析，而无需事先将数据导入到 PALO 中。

可以使用任意的 SQL 语句进行文件分析，如：

SELECT * FROM s3(
    'uri' = 's3://bucket/path/to/tvf_test/test.parquet',
    'format' = 'parquet',
    's3.endpoint' = 'https://s3.us-east-1.amazonaws.com',
    's3.region' = 'us-east-1',
    's3.access_key' = 'ak',
    's3.secret_key'='sk'
)
ORDER BY p_partkey LIMIT 5;
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
| p_partkey | p_name                                   | p_mfgr         | p_brand  | p_type                  | p_size | p_container | p_retailprice | p_comment           |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+
|         1 | goldenrod lavender spring chocolate lace | Manufacturer#1 | Brand#13 | PROMO BURNISHED COPPER  |      7 | JUMBO PKG   |           901 | ly. slyly ironi     |
|         2 | blush thistle blue yellow saddle         | Manufacturer#1 | Brand#13 | LARGE BRUSHED BRASS     |      1 | LG CASE     |           902 | lar accounts amo    |
|         3 | spring green yellow purple cornsilk      | Manufacturer#4 | Brand#42 | STANDARD POLISHED BRASS |     21 | WRAP CASE   |           903 | egular deposits hag |
|         4 | cornflower chocolate smoke green pink    | Manufacturer#3 | Brand#34 | SMALL PLATED BRASS      |     14 | MED DRUM    |           904 | p furiously r       |
|         5 | forest brown coral puff cream            | Manufacturer#3 | Brand#32 | STANDARD POLISHED TIN   |     15 | SM PKG      |           905 |  wake carefully     |
+-----------+------------------------------------------+----------------+----------+-------------------------+--------+-------------+---------------+---------------------+

TVF 可以出现在 SQL 中，Table 能出现的任意位置。如 CTE 的 WITH 子句中，FROM 子句中等等。这样，您可以把文件当做一张普通的表进行任意分析。

您也可以用过 CREATE VIEW 语句为 TVF 创建一个逻辑视图。之后，可以像其他视图一样，对这个 TVF 进行访问、权限管理等操作，也可以让其他用户访问这个 View，而无需重复书写连接信息等属性。

-- Create a view based on a TVF
CREATE VIEW tvf_view AS 
SELECT * FROM s3(
    'uri' = 's3://bucket/path/to/tvf_test/test.parquet',
    'format' = 'parquet',
    's3.endpoint' = 'https://s3.us-east-1.amazonaws.com',
    's3.region' = 'us-east-1',
    's3.access_key' = 'ak',
    's3.secret_key'='sk'
);

-- Describe the view as usual
DESC tvf_view;

-- Query the view as usual
SELECT * FROM tvf_view;

-- Grant SELECT priv to other user on this view
GRANT SELECT_PRIV ON db.tvf_view TO other_user;

数据导入

TVF 可以作为 PALO 数据导入方式的一种。配合 INSERT INTO SELECT 语法，我们可以很方便的将文件导入到 PALO 中。

-- Create a PALO table
CREATE TABLE IF NOT EXISTS test_table
(
    id int,
    name varchar(50),
    age int
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");

-- 2. Load data into table from TVF
INSERT INTO test_table (id,name,age)
SELECT cast(id as INT) as id, name, cast (age as INT) as age
FROM s3(
    'uri' = 's3://bucket/path/to/tvf_test/test.parquet',
    'format' = 'parquet',
    's3.endpoint' = 'https://s3.us-east-1.amazonaws.com',
    's3.region' = 'us-east-1',
    's3.access_key' = 'ak',
    's3.secret_key'='sk'
);

注意事项

如果指定的 uri 匹配不到文件，或者匹配到的所有文件都是空文件，那么 TVF 将会返回空结果集。在这种情况下使用DESC FUNCTION查看这个 TVF 的 Schema，会得到一列虚拟的列__dummy_col，该列无意义，仅作为占位符使用。
如果指定的文件格式为 csv，所读文件不为空文件但文件第一行为空，则会提示错误The first line is empty, can not parse column numbers，这是因为无法通过该文件的第一行解析出 Schema。

数据缓存数据库分析

Palo文档 PALO

PALO